﻿Public Class frmInvReport54
    Dim _Mysql As New ClsSQLhelper
    Dim Strsql As String
    Dim On_Load As Boolean = True
#Region "Function"
    'Sub Load_BudgetYear()
    '    Try
    '        Strsql = "select min(budget_year) from items group by budget_year limit 1"
    '        Dim MinYear As Integer
    '        MinYear = _mysql.MySQLExecuteScalar(Strsql)

    '        If MinYear = 0 Then
    '            Me.CboBudgetYear.Properties.Items.Add(Now.Year)
    '        Else
    '            For i As Integer = (Now.Year + 543) To MinYear Step -1
    '                Me.CboBudgetYear.Properties.Items.Add(i)
    '                i = +i
    '            Next
    '        End If
    '        Me.CboBudgetYear.Text = (Now.Year + 543)
    '        Me.DtpFDate.DateTime = "1/1/" & (Me.CboBudgetYear.Text)
    '        Me.DtpEDate.DateTime = "30/09/" & (Me.CboBudgetYear.Text) + 1
    '    Catch ex As Exception
    '        MsgBox(ex.Message)
    '    End Try
    'End Sub
    Sub Load_ItemStatus()
        Strsql = "Select Status_ID,Status_Name from itemstatus"
        Dim DT_ItemStatus As New DataTable
        DT_ItemStatus = _mysql.GetMYSQLDataTable(Strsql, "itemstatus")
        With Me.CboItemStatus
            .DataSource = DT_ItemStatus
            .DisplayMember = "Status_Name"
            .ValueMember = "Status_ID"
        End With
    End Sub
    Sub Load_BudgetType()
        Try
            Dim DT_Budget As New DataTable
            Strsql = "select Budget_ID,Budget_Name  from Budget order by Budget_ID "
            DT_Budget = _Mysql.GetMYSQLDataTable(Strsql, "sections")
            With Me.CboBudgetType
                .DataSource = DT_Budget
                .DisplayMember = "Budget_Name"
                .ValueMember = "Budget_ID"

            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Sub Load_Data()
        With GridItem
            Dim DT_Items As New DataTable
            Strsql = "Select items.Item_Type,items.Item_Code,Concat(items.Item_Type,items.Item_Code) as fullItemcode,Item_Name,Budget_year,Budget_Name,Item_Price,Receive_Date,Department_Name,"
            Strsql = Strsql & " Ref_DocReceiveID,Ref_DocDrawID,Discharge,discharge_item.Discharge_Date,Band,Item_EngName,Product_From_Country,Item_Model,Item_From_Company,Item_Desc,'' as companylist,'' as price_from_companylist"
            Strsql = Strsql & " from items "
            Strsql = Strsql & " left join discharge_item on Concat(items.Item_Type,items.Item_Code) = Concat(discharge_item.Item_Type,discharge_item.Item_Code)"
            Strsql = Strsql & " left join budget on items.Item_Budget = budget.Budget_ID "
            Strsql = Strsql & " left join department on items.department = department.department_id where "

            Select Case Me.ChkAllGroup.Checked
                Case True
                    Strsql = Strsql & " items.item_type Like '%' "
                Case False

                    Select Case Me.ChkAllClass.Checked
                        Case True
                            Strsql = Strsql & " items.item_type Like '" & Me.CboGroup.SelectedValue & "%' "
                        Case False
                            Strsql = Strsql & " items.item_type = '" & Me.CboClass.SelectedValue.ToString.Trim & "' "
                    End Select
            End Select

            Select Case ChkAllBudgetType.Checked
                Case True
                    '
                Case False
                    Strsql = Strsql & " and budget.Budget_ID =  " & Me.CboBudgetType.SelectedValue
            End Select

            Select Case ChkNonDate.Checked
                Case True
                    Select Case ChkAddNullReceiveDate.Checked
                        Case True
                            '
                        Case False
                            Strsql = Strsql & " and Receive_Date is not null "
                    End Select
                Case False
                    Strsql = Strsql & " and  (Receive_Date between '" & Pn_Framework.MysqlDateTimeFormat(Me.DtpFDate.DateTime, False) & "' and '" & Pn_Framework.MysqlDateTimeFormat(Me.DtpEDate.DateTime, False) & "'"
                    Select Case ChkAddNullReceiveDate.Checked
                        Case True
                            Strsql = Strsql & " or Receive_Date is null)"
                        Case False
                            Strsql = Strsql & ")"
                    End Select
            End Select

            Select Case Me.ChkNonPrice.Checked
                Case True
                    Select Case ChkAddNullPrice.Checked
                        Case True
                            '
                        Case False
                            Strsql = Strsql & " and Item_Price <> 0 "
                    End Select
                Case False
                    Strsql = Strsql & " and  (Item_Price >= " & Me.TxtPriceFrom.Value & " and  Item_Price <= " & Me.TxtPriceTo.Value

                    Select Case ChkAddNullPrice.Checked
                        Case True
                            Strsql = Strsql & " or Item_Price =0)"
                        Case False
                            Strsql = Strsql & ")"
                    End Select
            End Select

            Select Case ChkAllBudgetType.Checked
                Case True
                    '
                Case False
                    Strsql = Strsql & " and budget.Budget_ID =  " & Me.CboBudgetType.SelectedValue
            End Select

            DT_Items = _Mysql.GetMYSQLDataTable(Strsql, "Items")
            .DataSource = DT_Items
        End With
    End Sub

#End Region
    Private Sub frmInvReport54_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim DT_ItemGroup As New DataTable
        'Strsql = "Select Section_ID,Section_Name from sections order by section_id"
        Strsql = "select section_id,section_name  from sections order by section_id "
        DT_ItemGroup = _Mysql.GetMYSQLDataTable(Strsql, "sections")
        With Me.CboGroup
            .DataSource = DT_ItemGroup
            .DisplayMember = "section_name"
            .ValueMember = "Section_ID"

        End With

        Dim DT_Itemclass As New DataTable
        Strsql = "Select Type_ID,Type_Name,Concat(trim(Type_ID),' - ',trim(Type_Name)) as fullclassname from typeitem where type_id like '" & Me.CboGroup.SelectedValue & "%'"
        DT_Itemclass = _Mysql.GetMYSQLDataTable(Strsql, "typeitem")
        With Me.CboClass
            .DataSource = DT_Itemclass
            .DisplayMember = "fullclassname"
            .ValueMember = "Type_ID"
        End With

        Load_BudgetType()

        'Me.GridColumn1.FieldName = "Item_Type"
        'Me.GridColumn1.Caption = "รหัสชนิด"
        'Me.GridColumn1.OptionsColumn.ReadOnly = False
        'Me.GridColumn2.FieldName = "Item_Code"
        'Me.GridColumn2.Caption = "หมายเลขครุภัณฑ์"
        'Me.GridColumn2.OptionsColumn.ReadOnly = False
        'Me.GridColumn12.FieldName = "Item_Name"
        'Me.GridColumn12.Caption = "ชื่อครุภัณฑ์"
        'Me.GridColumn12.OptionsColumn.ReadOnly = False
        'Me.GridColumn3.FieldName = "Budget_year"
        'Me.GridColumn3.Caption = "ปีงบประมาณ"
        'Me.GridColumn3.OptionsColumn.ReadOnly = False
        'Me.GridColumn4.FieldName = "Budget_Name"
        'Me.GridColumn4.Caption = "ได้รับจากงบ"
        'Me.GridColumn4.OptionsColumn.ReadOnly = False
        'Me.GridColumn5.FieldName = "Item_Price"
        'Me.GridColumn5.Caption = "ราคาต่อหน่วย"
        'Me.GridColumn5.OptionsColumn.ReadOnly = False
        'Me.GridColumn6.FieldName = "Receive_Date"
        'Me.GridColumn6.Caption = "วันที่ได้รับ"
        'Me.GridColumn6.OptionsColumn.ReadOnly = False
        'Me.GridColumn7.FieldName = "Department_Name"
        'Me.GridColumn7.Caption = "แผนก/ฝ่าย"
        'Me.GridColumn7.OptionsColumn.ReadOnly = False
        'Me.GridColumn8.FieldName = "Ref_DocReceiveID"
        'Me.GridColumn8.Caption = "เลขที่เอกสารับ"
        'Me.GridColumn8.OptionsColumn.ReadOnly = False
        'Me.GridColumn9.FieldName = "Ref_DocDrawID"
        'Me.GridColumn9.Caption = "เลขที่เอกสารเบิก"
        'Me.GridColumn9.OptionsColumn.ReadOnly = False
        'Me.GridColumn10.FieldName = "Discharge"
        'Me.GridColumn10.Caption = "จำหน่าย"
        'Me.GridColumn10.OptionsColumn.ReadOnly = False
        'Me.GridColumn11.FieldName = "DischargeDate"
        'Me.GridColumn11.Caption = "วันที่จำหน่าย"
        'Me.GridColumn11.OptionsColumn.ReadOnly = False
        On_Load = False
        Load_Data()
        Load_ItemStatus()
    End Sub

    Private Sub BtnExport2Excel_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BtnExport2Excel.ItemClick
        With SaveFileDialog1
            .Title = "ส่งออกเป็น Excel"
            .FileName = "แบบฟอร์มสำรวจครุภัณฑ์การแพทย์.xls"
            .Filter = "Excel 2003 File | *.xls"
            .ShowDialog()
            Me.GridItem.ExportToXls(.FileName)
        End With
    End Sub

    Private Sub BtnSearch_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BtnSearch.ItemClick
        Load_Data()
        MsgBox("ดึงข้อมูลเรียบร้อยร้อย", MsgBoxStyle.Information, "ผลการดึงข้อมูล")
    End Sub

    Private Sub CboGroup_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CboGroup.SelectedIndexChanged
        If On_Load = True Then Exit Sub
        Dim DT_Itemclass As New DataTable
        Strsql = "Select Type_ID,Type_Name,Concat(trim(Type_ID),' - ',trim(Type_Name)) as fullclassname from typeitem  where type_id like '" & Me.CboGroup.SelectedValue & "%'"
        DT_Itemclass = _Mysql.GetMYSQLDataTable(Strsql, "typeitem")
        With Me.CboClass
            .DataSource = DT_Itemclass
            .DisplayMember = "fullclassname"
            .ValueMember = "Type_ID"
        End With
    End Sub

    Private Sub CboClass_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CboClass.SelectedIndexChanged
        Load_Data()
    End Sub

    Private Sub ChkNonDate_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChkNonDate.CheckedChanged
        If ChkNonDate.Checked = False Then
            DtpFDate.Enabled = True
            DtpFDate.DateTime = Now
            DtpEDate.Enabled = True
            DtpEDate.DateTime = Now
            Me.ChkAddNullReceiveDate.Enabled = True
            Me.ChkAddNullReceiveDate.Checked = True
        Else
            DtpFDate.Enabled = False

            DtpEDate.Enabled = False


            Me.ChkAddNullReceiveDate.Enabled = True
            Me.ChkAddNullReceiveDate.Checked = True

            Me.ChkAddNullPrice.Enabled = True
            Me.ChkAddNullPrice.Checked = True
        End If
    End Sub

    Private Sub ChkNonPrice_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChkNonPrice.CheckedChanged
        If ChkNonPrice.Checked = False Then
            TxtPriceFrom.Enabled = True
            TxtPriceTo.Enabled = True
            TxtPriceTo.Text = 9999999
            Me.ChkAddNullPrice.Enabled = True
            Me.ChkAddNullPrice.Checked = True
        Else
            TxtPriceFrom.Enabled = False
            TxtPriceTo.Enabled = False

            Me.ChkAddNullPrice.Enabled = True
            Me.ChkAddNullPrice.Checked = True
        End If
    End Sub

    Private Sub ChkAllGroup_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChkAllGroup.CheckedChanged
        If ChkAllGroup.Checked = True Then
            Me.ChkAllClass.Enabled = False
            Me.CboClass.Enabled = False
            Me.CboGroup.Enabled = False
            Me.ChkAllClass.Checked = True
            Me.ChkAddNullPrice.Checked = True
            'Me.ChkAddNullPrice.Enabled = False
            'Me.ChkAddNullReceiveDate.Enabled = False
            Me.ChkAddNullReceiveDate.Checked = True
        Else
            Me.ChkAllClass.Checked = False
            Me.ChkAllClass.Enabled = True
            Me.CboClass.Enabled = True
            Me.CboGroup.Enabled = True

            Me.ChkAddNullPrice.Checked = False
            'Me.ChkAddNullPrice.Enabled = True
            Me.ChkAddNullReceiveDate.Checked = False
            ' Me.ChkAddNullReceiveDate.Enabled = True


        End If
    End Sub

    Private Sub ChkAllClass_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChkAllClass.CheckedChanged
        If ChkAllClass.Checked = True Then
            Me.CboClass.Enabled = False

        Else
            Me.CboClass.Enabled = True

        End If
    End Sub

    Private Sub ChkAllBudgetType_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChkAllBudgetType.CheckedChanged
        If ChkAllBudgetType.Checked = True Then
            Me.CboBudgetType.Enabled = False

        Else
            Me.CboBudgetType.Enabled = True

        End If
    End Sub

    Private Sub GridItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GridItem.Click

    End Sub

    Private Sub GridView1_CustomColumnDisplayText(ByVal sender As System.Object, ByVal e As DevExpress.XtraGrid.Views.Base.CustomColumnDisplayTextEventArgs) Handles GridView1.CustomColumnDisplayText
        If e.Column.FieldName = "" Then
            e.DisplayText = e.RowHandle.ToString + 1
        End If

    End Sub

    Private Sub ChkAddNullReceiveDate_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChkAddNullReceiveDate.CheckedChanged

    End Sub

    Private Sub ChkAddNullPrice_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChkAddNullPrice.CheckedChanged

    End Sub
End Class